package com.xiaojian.pick.util;

import com.xiaojian.pick.entity.BondHistory;
import lombok.extern.slf4j.Slf4j;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.xssf.usermodel.XSSFDataValidation;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;

@Slf4j
public class ExcelUtils {

    /**
     * 获取工作簿对象
     * @param fileName
     * @return
     */
    public static List<BondHistory> getWorkbook(String fileName){
        if (!fileName.matches("^.+\\.(?i)(xls)$") && !fileName.matches("^.+\\.(?i)(xlsx)$")) {
            throw new RuntimeException("上传文件格式不正确");
        }
        boolean isExcel2003 = true;
        if (fileName.matches("^.+\\.(?i)(xlsx)$")) {
            isExcel2003 = false;
        }
        List<BondHistory> list = new ArrayList<>();
        Workbook wb = null;
        try( InputStream is = new FileInputStream(fileName);) {
            if (isExcel2003) {
                wb = new HSSFWorkbook(is);
            } else {
                wb = new XSSFWorkbook(is);
            }


            Sheet sheet = wb.getSheetAt(0);

            int rowNum = sheet.getLastRowNum();

            DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy.MM.dd HH:mm:ss");
            for (int i = 1; i < rowNum; i++){
                Row row = sheet.getRow(i);
                String date = row.getCell(0).getStringCellValue().trim() + " 00:00:00";
                list.add(new BondHistory()
                         //.setCreateTime(LocalDateTime.parse(date, formatter))
                        .setSharer(row.getCell(2).getStringCellValue())
                        .setReceive(row.getCell(4).getStringCellValue())
                        .setTitle(row.getCell(3).getStringCellValue()));
            }
        } catch (IOException e) {
            log.error("获取工作簿对象失败：{}", e.getMessage(), e);
            throw new RuntimeException("获取工作簿对象失败,请重试");
        }finally {
        }
        return list;
    }




    public static XSSFWorkbook setDataValidation(XSSFWorkbook workBook,String[] strArr,String sheetName,String name,int firstRow,int lastRow,int firstCol,int lastCol){

        // 创建sheet，写入枚举项
        Sheet hideSheet = workBook.createSheet(sheetName);
        for (int i = 0; i < strArr.length; i++) {
            hideSheet.createRow(i).createCell(0).setCellValue(strArr[i]);
        }
        // 创建名称，可被其他单元格引用
        Name category1Name = workBook.createName();
        category1Name.setNameName(name);
        category1Name.setRefersToFormula(sheetName+"!" + "$A$1:$A$" + strArr.length);
        // 获取上文名称内数据
        XSSFSheet sheet0 = workBook.getSheetAt(0);
        DataValidationHelper helper = sheet0.getDataValidationHelper();
        DataValidationConstraint constraint = helper.createFormulaListConstraint(name);
        // 设置下拉框位置
        CellRangeAddressList addressList = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
        DataValidation dataValidation = helper.createValidation(constraint, addressList);
        if (dataValidation instanceof XSSFDataValidation) {
            // 数据校验
            dataValidation.setSuppressDropDownArrow(true);
            dataValidation.setShowErrorBox(true);
        }
        sheet0.addValidationData(dataValidation);
        // 设置hiddenSheet隐藏
        workBook.setSheetHidden(1, true);
        return  workBook;
    }

    public static void main(String[] args) {
        getWorkbook("F:\\关照闲置物品结缘表0311.xlsx");
    }
}
